﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BinhSon.Dao;
using BinhSon.Services.Const;

namespace BinhSon.Services.Impl
{
    public  partial class BinhSonServices
    {
        #region public methods
        public bool ImportDiemThi(DataSet ds)
        {
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    var itemArray = row.ItemArray;
                    var maHocSinh = itemArray[0].ToString();
                    var soDiem = Convert.ToDouble(itemArray[1]);

                    //process to diem
                    soDiem = this.ProcessToDiem(soDiem);

                    var maSoHocSinh = this._bsEntities.MaSoHocSinh.SingleOrDefault(x => x.MaHocSinh == maHocSinh);

                    if (maSoHocSinh == null)
                    {
                        continue;
                    }

                    //kiem tra diem nay co chua
                    var diemId = this._bsEntities.DiemThi.Where(x => x.HeSo == maSoHocSinh.MonHocCuaHocKy.HeSo && x.SoDiem == soDiem)
                            .Select(x => x.DiemId)
                            .SingleOrDefault();
                    if (diemId == 0)
                    {
                        //tao ra diem moi
                        var diemThiEntity = new DiemThi
                        {
                            HeSo = maSoHocSinh.MonHocCuaHocKy.HeSo,
                            SoDiem = soDiem
                        };
                        this._bsEntities.DiemThi.Add(diemThiEntity);
                        this._bsEntities.SaveChanges();
                        diemId = diemThiEntity.DiemId;
                    }
                    //kiem tra diem ton tai chua
                    var diemHocSinhOld =
                        this._bsEntities.DiemSoHocSinh.SingleOrDefault(
                            x =>
                                x.HocSinhId == maSoHocSinh.HocSinhId &&
                                x.MonHocCuaHocKyId == maSoHocSinh.MonHocCuaHocKyId);
                    if (diemHocSinhOld != null)
                    {
                        if (diemHocSinhOld.DiemId != diemId)
                        {
                            diemHocSinhOld.DiemId = diemId;
                            this._bsEntities.SaveChanges();
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        //tao ra diem so hoc sinh
                        var diemHocSinh = new DiemSoHocSinh
                        {
                            DiemId = diemId,
                            HocSinhId = maSoHocSinh.HocSinhId,
                            MonHocCuaHocKyId = maSoHocSinh.MonHocCuaHocKyId
                        };
                        this._bsEntities.DiemSoHocSinh.Add(diemHocSinh);
                        this._bsEntities.SaveChanges();    
                    }
                }
            }
            return true;
        }        

        public IList<SystemLookup> GetListOfHeSoDiem()
        {
            return this._bsEntities.SystemLookup.Where(x => x.LookupTypeId == (int)SystemLookupConst.HeSoDiem).ToList();
        }

        public DataSet ExportBangChamDiem(int hocKyId, int khoiId, int monHocId, int lanThiId, int heSo)
        {
            var dataSet = new DataSet();            
            var currentYear = this._bsEntities.ConstantTable.Select(x => x.NamHocHienTai).SingleOrDefault();
            var mhchk =
                this._bsEntities.MonHocCuaHocKy.SingleOrDefault(
                    x =>
                        x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                        x.MonHocId == monHocId && x.LanThiId == lanThiId && x.HeSo == heSo);
            if (mhchk == null)
            {
                return dataSet;
            }
            //get ds phong thi
            var dsPhongThi = this._bsEntities.PhongThi.Where(x => x.MonHocCuaHocKyId == mhchk.MonHocCuaHocKyId);
            var count = 1;
            foreach (var phongThi in dsPhongThi)
            {
                var dataTable = new DataTable(phongThi.MaPhongThi);
                dataSet.Tables.Add(dataTable);
                dataTable.Columns.Add("Mã học sinh");
                dataTable.Columns.Add("Điểm số");
                //get ds hoc sinh cua phong thi
                var dsHocSinh =
                    this._bsEntities.PhongThiHocSinh.Where(x => x.PhongThiId == phongThi.PhongThiId).ToList();
                foreach (var phongThiHocSinh in dsHocSinh)
                {
                    var maSoHocSinh = phongThi.MaPhongThi + phongThiHocSinh.SoThuTu;
                    var dataRow = dataTable.NewRow();                    
                    dataRow[0] = maSoHocSinh;
                    dataTable.Rows.Add(dataRow);
                    //create ma so hoc sinh
                    var oldentity =
                        this._bsEntities.MaSoHocSinh.SingleOrDefault(
                            x =>
                                x.MonHocCuaHocKyId == mhchk.MonHocCuaHocKyId && x.HocSinhId == phongThiHocSinh.HocSinhId &&
                                x.MaHocSinh == maSoHocSinh);
                    if (oldentity == null)
                    {
                        var newEntity = new MaSoHocSinh
                        {
                            MonHocCuaHocKyId = mhchk.MonHocCuaHocKyId,
                            HocSinhId = phongThiHocSinh.HocSinhId,
                            MaHocSinh = maSoHocSinh
                        };
                        this._bsEntities.MaSoHocSinh.Add(newEntity);
                    }
                }
                count++;
            }
            this._bsEntities.SaveChanges();
            return dataSet;
        }

        public DataSet ExportFileMaHoa(int hocKyId, int khoiId, int monHocId, int lanThiId, int heSo)
        {
            var dataSet = new DataSet();
            var currentYear = this._bsEntities.ConstantTable.Select(x => x.NamHocHienTai).SingleOrDefault();
            var mhchk =
                this._bsEntities.MonHocCuaHocKy.SingleOrDefault(
                    x =>
                        x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                        x.MonHocId == monHocId && x.LanThiId == lanThiId && x.HeSo == heSo);
            if (mhchk == null)
            {
                return dataSet;
            }
            //get ds phong thi
            var dsPhongThi = this._bsEntities.PhongThi.Where(x => x.MonHocCuaHocKyId == mhchk.MonHocCuaHocKyId);
            var count = 1;
            var dataTable = new DataTable();
            dataSet.Tables.Add(dataTable);
            dataTable.Columns.Add("Lớp");
            dataTable.Columns.Add("Tên phòng");
            dataTable.Columns.Add("Mã phòng");             
            foreach (var phongThi in dsPhongThi)
            {                
                var dataRow = dataTable.NewRow();
                dataRow[0] = phongThi.LopHoc.TenLop;
                dataRow[1] = "Phòng " + count;
                dataRow[2] = phongThi.MaPhongThi;
                dataTable.Rows.Add(dataRow);
                count++;
            }
            return dataSet;
        }

        public DataSet ExportBangDiemThi(int hocKyId, int khoiId, int monHocId, int? lopHocId = null, int? lanThiId = null, int? heSo = null)
        {
            var currentYear = this._bsEntities.ConstantTable.Select(x => x.NamHocHienTai).SingleOrDefault();
            var dsMhchk = new List<MonHocCuaHocKy>();
            if (lanThiId.HasValue && heSo.HasValue)
            {
                dsMhchk =
                    this._bsEntities.MonHocCuaHocKy.Where(
                        x =>
                            x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                            x.MonHocId == monHocId && x.LanThiId == lanThiId.Value && x.HeSo == heSo.Value).ToList();
            }
            if (lanThiId.HasValue && !heSo.HasValue)
            {
                dsMhchk =
                    this._bsEntities.MonHocCuaHocKy.Where(
                        x =>
                            x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                            x.MonHocId == monHocId && x.LanThiId == lanThiId.Value).ToList();
            }
            if (!lanThiId.HasValue && heSo.HasValue)
            {
                dsMhchk =
                    this._bsEntities.MonHocCuaHocKy.Where(
                        x =>
                            x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                            x.MonHocId == monHocId && x.HeSo == heSo.Value).ToList();
            }
            if (!lanThiId.HasValue && !heSo.HasValue)
            {
                dsMhchk =
                    this._bsEntities.MonHocCuaHocKy.Where(
                        x =>
                            x.NamHocId == currentYear && x.HocKyId == hocKyId && x.KhoiId == khoiId &&
                            x.MonHocId == monHocId).ToList();
            }
            var dataSet = new DataSet();
            if (lopHocId.HasValue)
            {
                var lopHoc = this._bsEntities.LopHoc.SingleOrDefault(x => x.LopHocId == lopHocId.Value);
                if (lopHoc == null)
                {
                    return new DataSet();
                }
                var dataTable = this.ExportBangDiemThiTheoLop(dsMhchk, lopHoc);
                dataSet.Tables.Add(dataTable);
            }
            else
            {
                //export cho tat ca cac lop
                var dsLop = this._bsEntities.LopHoc.Where(x => x.KhoiId == khoiId).ToList();
                foreach (var lopHoc in dsLop)
                {
                    var dataTable = this.ExportBangDiemThiTheoLop(dsMhchk, lopHoc);
                    dataSet.Tables.Add(dataTable);
                }
            }
            return dataSet;
        }
        #endregion

        #region private methods        
        private DataTable ExportBangDiemThiTheoLop(IList<MonHocCuaHocKy> dsMhchk, LopHoc lopHoc)
        {
            var dataTable = new DataTable(lopHoc.TenLop);
            var dsHocSinh = this._bsEntities.HocSinh.Where(x => x.LopHocId == lopHoc.LopHocId).ToList();
            
            dataTable.Columns.Add("Lớp");
            dataTable.Columns.Add("Số thứ tự", typeof(int));
            dataTable.Columns.Add("Họ và chữ lót");
            dataTable.Columns.Add("Tên");
            dataTable.Columns.Add("Giới tính");

            var firstMhchk = dsMhchk.FirstOrDefault();
            if (firstMhchk == null)
            {
                return dataTable;
            }
            var diem15Phut = dsMhchk.Count(x => x.HeSo == (int) HeSoDiem.Diem15Phut);
            var diem1Tiet = dsMhchk.Count(x => x.HeSo == (int)HeSoDiem.Diem1Tiet);
            var diemCuoiKy = dsMhchk.Count(x => x.HeSo == (int)HeSoDiem.DiemCuoiKy);
            for (var i = 0; i < diem15Phut; i++)
            {
                dataTable.Columns.Add(firstMhchk.MonHoc.KyHieuMonHoc + " 15 phút " + this.GenerateSoHaiChuSo(i + 1));
            }
            for (var i = 0; i < diem1Tiet; i++)
            {
                dataTable.Columns.Add(firstMhchk.MonHoc.KyHieuMonHoc + " 1 tiết " + this.GenerateSoHaiChuSo(i + 1));
            }
            for (var i = 0; i < diemCuoiKy; i++)
            {
                dataTable.Columns.Add("Điểm cuối kỳ " + this.GenerateSoHaiChuSo(i + 1));
            }

            foreach (var hocSinh in dsHocSinh)
            {
                var dataRow = dataTable.NewRow();
                var gioiTinh = hocSinh.GioiTinh ? "Nam" : "Nữ";
                dataRow[0] = lopHoc.TenLop;
                dataRow[1] = hocSinh.SoThuTu;
                dataRow[2] = hocSinh.HoVaChuLot;
                dataRow[3] = hocSinh.Ten;
                dataRow[4] = gioiTinh;

                var dsMhchk15Phut = dsMhchk.Where(x => x.HeSo == (int) HeSoDiem.Diem15Phut).Select(x => x.MonHocCuaHocKyId);
                var dsMhchk1Tiet = dsMhchk.Where(x => x.HeSo == (int)HeSoDiem.Diem1Tiet).Select(x => x.MonHocCuaHocKyId);
                var dsMhchkCuoiKy = dsMhchk.Where(x => x.HeSo == (int)HeSoDiem.DiemCuoiKy).Select(x => x.MonHocCuaHocKyId);
                var dsDiem15Phut = this._bsEntities.DiemSoHocSinh.Where(x => x.HocSinhId == hocSinh.HocSinhId && dsMhchk15Phut.Contains(x.MonHocCuaHocKyId)).ToList();
                for (var i = 0; i < dsDiem15Phut.Count; i++)
                {
                    dataRow[4 + i + 1] = dsDiem15Phut[i].DiemThi.SoDiem;
                }
                var dsDiem1Tiet = this._bsEntities.DiemSoHocSinh.Where(x => x.HocSinhId == hocSinh.HocSinhId && dsMhchk1Tiet.Contains(x.MonHocCuaHocKyId)).ToList();
                for (var i = 0; i < dsDiem1Tiet.Count; i++)
                {
                    dataRow[4 + diem15Phut + i + 1] = dsDiem1Tiet[i].DiemThi.SoDiem;
                }
                var dsDiemCuoiKy = this._bsEntities.DiemSoHocSinh.Where(x => x.HocSinhId == hocSinh.HocSinhId && dsMhchkCuoiKy.Contains(x.MonHocCuaHocKyId)).ToList();
                for (var i = 0; i < dsDiemCuoiKy.Count; i++)//1
                {
                    dataRow[4 + diem15Phut + diem1Tiet + i + 1] = dsDiemCuoiKy[i].DiemThi.SoDiem;
                }
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }

        private double ProcessToDiem(double diem)
        {
            if (diem > 10)
            {
                return this.ProcessToDiem(diem/10);
            }
            if (diem < 0)
            {
                return 0;
            }
            return diem;
        }
        #endregion
    }
}
